*** Settings ***
Documentation    TC03-Data Cleaning FCL Project By Port
Resource    ../../Keywords/Common_Head.robot
Force Tags    P1
Metadata    Author    Arya
Metadata    ID    6850933

*** Variables ***
# ${G_TENDERID}    AUTO 30248

*** Test Cases ***
TC03-Data Cleaning FCL Project By Port
    Step 1 Login Space and Create the Tender
    Step 2 Go To Pricing Project to Create Project
    Step 3 Input Following Attributes for Project
    Step 4 Select The Specified Row to Enable the data cleaning button
    Step 5 Click Data Cleaning Button
    Step 6 Click Upload Button
    Step 7 Upload File
    Step 8 Select Port Routing
    Step 9 Click Start Button
    Step 10 Click Download Button
    Step 11 Check The Values Of Download File
    Step 12 Modify The Download File And Upload the file
    Step 13 Click Start Button Again
    Step 14 Download File Again
    Step 15 Click Close Button
    Step 16 Click Yes to Close the dialog
    Step 17 Find the Project in Pricing Project page
    Step 18 Delete the Project
    Step 19 Delete the Tender


*** Keywords ***
Step ${step} Login Space and Create the Tender
    #************************Step 1******************************
    #  Do:
    #      User goes to Basic Data Management Tender List
    #      Click Create button
    #      Input with following attributes
    #      Tender ID some numbers such as 202408151435001 you can
    #     input with current datetime and some random number to
    #     avoid duplicate validation
    #      Tender Title some value such as Regression Test by
    #     Wendy001
    #      Customer 815 Miele
    #      Mode select all
    #      Level Global
    #      Click on Save button
    #  Expected:
    #      Created successfully with the message Tender has been
    #     added
    #************************************************************
    Login Space
    ${G_TENDERID}    Generate Tender ID
    ${randomInt}    Evaluate    random.randint(0, 10000)
    ${TenderTitle}   Set Variable    Regression Test by Auto_${randomInt}
    Set Test Variable     ${G_TENDERID}
    Set Test Variable    ${TenderTitle}
    Create Tender    ${G_TENDERID}    ${TenderTitle}    Miele    true    true    true

Step ${step} Go To Pricing Project to Create Project
    #************************Step 2******************************
    #  Do:
    #      User goes to Pricing Collaboration Pricing Project
    #      User clicks Create Project button
    #  Expected:
    #      A New Project dialog should be opened successfully
    #************************************************************
    ${curDate}    Get Current Date    result_format=%d/%m/%Y
    Set Test Variable    ${curDate}
    ${DEADLINE_TM_DATE}    Get Current Date    UTC    +10days    result_format=%d/%m/%Y
    ${toDate}    Get Current Date    UTC    +100days    result_format=%d/%m/%Y

    ${PROJECT}    Copy Dictionary    ${PROJECT_PARAM}
    Set To Dictionary    ${PROJECT}
    ...    TENDER_ID=${G_TENDERID}    TENDER_TITLE=${TenderTitle}    CUSTOEMR_NAME=Miele    CUSTOMER_LEVEL=Global
    ...    MODE=FCL    DEPUTY=NATZHANG
    ...    VALID_FROM=${curDate}    VALID_TO=${toDate}    DEADLINE_TM_DATE=${DEADLINE_TM_DATE}
    Set Test Variable    ${PROJECT}

Step ${step} Input Following Attributes for Project
    #************************Step 3******************************
    #  Do:
    #      Input following attributes
    #      Tender ID 20240815 Regression
    #      Mode Air
    #      Deadline to TM current date 10 day
    #      Rate Validity From current date
    #      Rate Validity To current date 100 day
    #      Deputies NATZHANG
    #      Description for regression
    #      Click on Save button
    #  Expected:
    #      A new project is created successfully
    #************************************************************
    Create Project   ${PROJECT}

Step ${step} Select The Specified Row to Enable the data cleaning button
    #************************Step 4******************************
    #  Do:
    #      User select the row which project name is 20240815 FCL
    #     Regression
    #  Expected:
    #      The row is selected and the button of data cleaning is
    #     enabled
    #************************************************************
    Search Project    ${G_TENDERID}
    Get Element States    ${Project_toolbar_DataCleaningButton}    contains    visible

Step ${step} Click Data Cleaning Button
    #************************Step 5******************************
    #  Do:
    #      User clicks data cleaning button
    #  Expected:
    #      A dialogue named 20240815 FCL Regress shows
    #     successfully
    #************************************************************
    Click    ${Project_toolbar_DataCleaningButton}
    Get Element States    ${Data_toolbar_UploadButton}    contains    visible
    Check Text Value    //h4[@class='modal-title ng-binding']    ${G_TENDERID}-FCL-${TenderTitle}

Step ${step} Click Upload Button
    #************************Step 6******************************
    #  Do:
    #      User clicks upload button
    #  Expected:
    #      A dialogue name Upload Template shows successfully
    #************************************************************
    Click    ${Data_toolbar_UploadButton}
    Wait For Elements State    ${Data_Upload_AddFile}    enabled    60
    Check Text Value    //h4[@class='modal-title']    Upload Template

Step ${step} Upload File
    #************************Step 7******************************
    #  Do:
    #      User clicks add file button and select the file
    #     FCLTemplate Port All xlsx form local
    #  Expected:
    #      A table shows below upload button It contains document
    #     name and date time Document name is FCLTemplate Location
    #     All xlsx date time is you uploaded time like 03 10 2018
    #     17 05
    #************************************************************
    ${Data_Upload_AddFile1}    Set Variable    xpath=//input[@name='file']
    Upload File By Selector    ${Data_Upload_AddFile1}    ${CURDIR}${/}TestData${/}FCLTemplate-Port-All.xlsx
    Wait For Elements State    xpath=//label[.='Document Name']    visible    30 
    Wait For Elements State    //label[.='FCLTemplate-Port-All.xlsx']    visible    20
    Wait For Elements State    //label[contains(text(),'${curDate}')]    visible    20 

Step ${step} Select Port Routing
    #************************Step 8******************************
    #  Do:
    #      User select port routing
    #      Select all of them
    #      User select map columns like that
    #      Origin Country country from D
    #      Destination Country country to G
    #      Origin Port origin Port C
    #      Destination Port destination port F
    #      Origin State state from E
    #      Destination State state to H
    #      Origin Type origin type K
    #      Destination Type des type L
    #      Origin Postal Code Origin Postal Code M
    #      Destination Postal Code Destination Postal Code N
    #      Customer s Delivery Term Customer s Delivery Term O
    #      Customer Origin Place Customer Origin Place I
    #      Customer Destination Place customer des place J
    #      Customer Lane ID Lane ID code B
    #      Volume Containers 20 Volume Containers 20 R
    #      Volume Containers 40 Volume Containers 40 S
    #      Volume Containers 40HC Volume Containers 40HC T
    #      Volume Containers 45 Volume Containers 45 U
    #      Volume TEU Total Volume TEU Total V
    #      Commodity Description Commodity Description W
    #      IMO Class IMO Class X
    #      IMO UN No Y
    #  Expected:
    #      They both should be select successfully
    #************************************************************
    click    ${Select_By_Port}
    Check Check Box for Routing
    Wait Until Keyword Succeeds    20x    100ms    Select Options By   ${Origin_Country}    label    ${SPACE}${SPACE}country from - D
    Select Options By   ${Destination_Country}      label    ${SPACE}${SPACE}country to - G
    Select Options By   ${Origin_Port}      label    ${SPACE}origin port - C
    Select Options By   ${Destination_Port}     label    ${SPACE}destination port - F
    Select Options By   ${Origin_State}      label    State From - E
    Select Options By   ${Destination_State}      label    State To - H
    Select Options By   ${Origin_Type}    label    origin type - K
    Select Options By   ${Destination_Type}    label    des type - L
    Select Options By   ${Origin_Postal_Code}    label    Origin Postal Code - M
    Select Options By   ${Destination_Postal_Code}    label    Destination Postal Code - N
    Select Options By   ${Customer_Deliver_Term}    label    Customer's\nDelivery\nTerm - O
    Select Options By   ${Customer_Orign_Place}    label    customer origin place - I
    Select Options By   ${Customer_Destination_Place}    label    customer des place - J
    Select Options By   ${Customer_Lane_ID}    label    Lane ID code - B
    Select Options By   ${Volume_Containers_20}    label    Volume\nContainers\n20 - R
    Select Options By   ${Volume_Containers_40}    label    Volume\nContainers\n40 - S
    Select Options By   ${Volume_Containers_40HC}    label    Volume\nContainers\n40HC - T
    Select Options By   ${Volume_Containers_45}    label    Volume Containers 45 - U
    Select Options By   ${Volume_TEU_Total}    label    Volume\nTEU\nTota - V
    Select Options By   ${Commodity_Desc}    label    Commodity\nDescription - W
    Select Options By   ${IMO_Class}    label    IMO Class - X
    Select Options By   ${IMO_UN_No}      label    IMO UN No. - Y

Step ${step} Click Start Button
    #************************Step 9******************************
    #  Do:
    #      User clicks start button
    #  Expected:
    #      In the tab of step3 validation download upload and set
    #     pass buttons appears The summary shows successful
    #************************************************************
    click    ${Data_toolbar_StartButton}
    Sleep    2
    Wait For Elements State    ${Data_toolbar_DownloadButton}    visible    30 
    Wait For Elements State   ${Data_toolbar_UploadAfterStartButton}    visible
    Wait For Elements State    ${Data_toolbar_SetPassButton}     visible
    ${content}    Get Text    //div[@class="row form-group col-md-12 ng-scope"][2]
    Should Contain    ${content}    Summary: Successful
    Should Contain    ${content}    Please close this page and go to Generate Internal Pricing Template.

Step ${step} Click Download Button
    #************************Step 10******************************
    #  Do:
    #      User clicks download button
    #  Expected:
    #      File download successfully and the file named
    #     FCL_data_cleaning_template xlsx
    #************************************************************
    ${file_path}    Get Download ZIP File Name    ${G_TENDERID}    .zip
    Should Contain    ${file_path}   ${G_TENDERID}-FCL-Regress
    Set Test Variable    ${file_path}    ${file_path}

Step ${step} Check The Values Of Download File
    #************************Step 11******************************
    #  Do:
    #      Check the values of download file with the original
    #     file FCLTemplate Port All xlsx
    #  Expected:
    #      Compare the two files the value of the same column
    #     should be equal
    #************************************************************

    # Read the value from testdata file.
    Open Excel Document    ${CURDIR}${/}TestData${/}FCLTemplate-Location-All.xlsx    FCL
    ${2_rowsValues}    Read Excel Row    2
    ${3_rowsValues}    Read Excel Row    3
    Remove From List    ${2_rowsValues}    7
    Remove From List    ${2_rowsValues}    4
    Remove From List    ${2_rowsValues}    1
    Remove From List    ${2_rowsValues}    0
    Remove From List    ${3_rowsValues}    7
    Remove From List    ${3_rowsValues}    4
    Remove From List    ${3_rowsValues}    1
    Remove From List    ${3_rowsValues}    0
    Close All Excel Documents
    Log    ${2_rowsValues}
    Log    ${3_rowsValues}

    ${DOWNLOAD_PATH}     Normalize Path    ${DOWNLOAD_PATH}
    Extract Zip File    ${file_path}    ${DOWNLOAD_PATH}
    ${FILES}    List Files In Directory   ${DOWNLOAD_PATH}   *.xlsx
    ${excelFile}     Set Variable    ${DOWNLOAD_PATH}${/}${FILES}[0]
    Set Test Variable    ${excelFile}    ${excelFile}
    Wait Until Keyword Succeeds    50    1    Open Excel Document    ${excelFile}    FCL
    # Read Excel Row    1
    ${colList}    Create List    Origin\nCity    Origin\nCountry    Destination\nCity    Destination\nCountry    Origin\nType    Destination\nType
    ...    Origin\nPostalCode    Destination\nPostalCode    Customer's\nDelivery\nTerm    Customer\nPort of Load\n(POL)
    ...    Customer\nPort of Discharge\n(POD)    Volume\nContainers\n20    Volume\nContainers\n40    Volume\nContainers\n40HC    Volume\nContainers\n45
    ...    Volume\nTEU\nTotal    Commodity\nDescription    IMO Class    IMO UN No.
    ${len}    Get Length    ${colList}
    
    # Because don't select P & Q in step8, the corresponding value won't mapping into the downloaded file.
    # The corresponding values (P & Q column value in the uploaded file) are empty.
    ${P_index}    Get Index From List    ${colList}    Customer\nPort of Load\n(POL)
    ${Q_index}    Get Index From List    ${colList}    Customer\nPort of Discharge\n(POD)

    FOR    ${index}    IN RANGE    0    ${len}    1
        Log    ${index}
        ${cell_value1}    Read Excel cell by row index and Column Value    2    ${colList}[${index}]    FCL
        ${cell_value2}    Read Excel cell by row index and Column Value    3    ${colList}[${index}]    FCL
        
        IF    '${index}' == '0'
            Should Be Equal As Strings    ${cell_value1}    Shanghai
            Should Be Equal As Strings    ${cell_value2}    ${3_rowsValues}[${index}]
        ELSE IF    '${index}' == '${P_index}'
            Should Be Equal As Strings    ${cell_value1}    ${EMPTY}
            Should Be Equal As Strings    ${cell_value2}    ${EMPTY}
        ELSE IF    '${index}' == '${Q_index}'
            Should Be Equal As Strings    ${cell_value1}    ${EMPTY}
            Should Be Equal As Strings    ${cell_value2}    ${EMPTY}
        ELSE IF    ${index} < 6
            Should Be Equal As Strings    ${cell_value1}    ${2_rowsValues}[${index}]
            Should Be Equal As Strings    ${cell_value2}    ${3_rowsValues}[${index}]
        ELSE IF    ${index} < 9
            Should Be Equal As Integers    ${cell_value1}    ${2_rowsValues}[${index}]
            Should Be Equal As Integers    ${cell_value2}    ${3_rowsValues}[${index}]
        ELSE
            Should Be Equal As Strings    ${cell_value1}    ${2_rowsValues}[${index}]
            Should Be Equal As Strings    ${cell_value2}    ${3_rowsValues}[${index}]
        END
    END


Step ${step} Modify The Download File And Upload the file
    #************************Step 12******************************
    #  Do:
    #      Modify the download file and user clicks upload button
    #  Expected:
    #      The start button appears
    #************************************************************
    Write Excel Cell    2    27    189    FCL
    Save Excel Document   ${excelFile}
    Close All Excel Documents
    Data Upload Template After Modified    ${G_TENDERID}-FCL-Regress   ${excelFile}
    Wait For Elements State   ${Data_toolbar_StartButton}     enabled

Step ${step} Click Start Button Again
    #************************Step 13******************************
    #  Do:
    #      User click start button
    #  Expected:
    #      Wait a few seconds there is a successful prompt below
    #     the buttons like that Summary Successful
    #      Please close this page and go to Generate Internal
    #     Pricing Template
    #************************************************************
    Click    ${Data_toolbar_StartButton}
    ${content}    Get Text    //div[@class="row form-group col-md-12 ng-scope"][2]
    Should Contain    ${content}    Summary: Successful
    Should Contain    ${content}    Please close this page and go to Generate Internal Pricing Template.

Step ${step} Download File Again
    #************************Step 14******************************
    #  Do:
    #      User click download button
    #  Expected:
    #      File download successfully Check the modified area the
    #     values are equal with the modified values
    #************************************************************
    Remove All Files
    ${zipFile}    Get Download ZIP File Name    ${G_TENDERID}    .zip
    Check Modified Excel File    ${zipFile}    2    27    FCL    189


Step ${step} Click Close Button
    #************************Step 15******************************
    #  Do:
    #      User clicks close button
    #  Expected:
    #      A warning dialogue display and ask you Are you sure to
    #     close this page
    #************************************************************
    click    ${Data_toolbar_CloseButton} 

Step ${step} Click Yes to Close the dialog
    #************************Step 16******************************
    #  Do:
    #      User clicks yes when asking Are you sure to close this
    #     page
    #  Expected:
    #      The dialogue disappears
    #************************************************************
    click    //button//span[.='Yes']

Step ${step} Find the Project in Pricing Project page
    #************************Step 17******************************
    #  Do:
    #      In Pricing Project page User selects TenderID Contains
    #     20240815
    #  Expected:
    #      The project 20240815 FCL Regression could be found
    #************************************************************
    Wait For Load State
    Wait Until Keyword Succeeds    30    1    click    //td[contains(text(),'${G_TENDERID}')]
    

Step ${step} Delete the Project
    #************************Step 18******************************
    #  Do:
    #      Select this project click on Delete Project button
    #      Click Yes of the pop up confirm window
    #  Expected:
    #      System displays Project has been deleted
    #************************************************************
    Log    Done in teardown

Step ${step} Delete the Tender
    #************************Step 19******************************
    #  Do:
    #      User goes to Basic Data Management Tender List
    #      Search out the tender created in step1
    #      Click on Delete button
    #      Click on Yes button in the popup dialog
    #  Expected:
    #      System displays Tender has been deleted
    #************************************************************
    Log    Done in teardown

